﻿Public Class masprename
    Private prenameID = Nothing
    Private STPRENAME = Nothing
    Private SEPRENAME = Nothing
    Private FTPRENAME = Nothing
    Private FEPRENAME = Nothing
    Private SEX = Nothing
    Private prenameStatus = Nothing
    'Private selectMasprename = "SELECT PRENAME, FTPRENAME, FEPRENAME, STPRENAME, SEPRENAME, SEX, convert(bit, [STATUS]) FROM MASPRENAME"
    Private selectMasprename = "SELECT  masprename.`prename` as 'รหัสคำนำหน้า',masprename.`stprename` as 'คำนำหน้าไทย(ย่อ)',masprename.`seprename` as 'คำนำหน้าอังกฤษ(ย่อ)',masprename.`ftprename` as 'คำนำหน้าไทย(เต็ม)',masprename.`feprename` as 'คำนำหน้าอังกฤษ(เต็ม)',massex.`sexdesc` as 'เพศ',masprename.`status` as 'สถานะการเปิดใช้งาน',massex.`sex` as 'SEX' FROM masprename LEFT JOIN (SELECT * FROM  massex WHERE `status` = 1) as massex ON masprename.sex = massex.sex  ORDER BY prename "
    Dim connect As CONDB = CONDB.NewConnection()
    'Dim connectlocal As conlocal = conlocal.NewConnection

    Property prenameID_ As Integer
        Get
            Return prenameID
        End Get
        Set(value As Integer)
            prenameID = value
        End Set
    End Property

    Property STPRENAME_ As String
        Get
            Return STPRENAME
        End Get
        Set(value As String)
            STPRENAME = value
        End Set
    End Property

    Property SEPRENAME_ As String
        Get
            Return SEPRENAME
        End Get
        Set(value As String)
            SEPRENAME = value
        End Set
    End Property

    Property FTPRENAME_ As String
        Get
            Return FTPRENAME
        End Get
        Set(value As String)
            FTPRENAME = value
        End Set
    End Property

    Property FEPRENAME_ As String
        Get
            Return FEPRENAME
        End Get
        Set(value As String)
            FEPRENAME = value
        End Set
    End Property

    Property SEX_ As Integer
        Get
            Return SEX
        End Get
        Set(value As Integer)
            SEX = value
        End Set
    End Property

    Property prenameStatus_ As Boolean
        Get
            Return prenameStatus
        End Get
        Set(value As Boolean)
            If value = True Then
                prenameStatus = 1
            Else
                prenameStatus = 0
            End If
        End Set
    End Property

    Property selectMasprename_ As String
        Get
            Return selectMasprename
        End Get
        Set(value As String)

        End Set
    End Property

    Public Sub loadcombobox(ByVal MassexCmb As ComboBox)
        Dim MASSEX As DataTable
        MASSEX = connect.GetTable("SELECT `sex`, `sexdesc` FROM massex WHERE `status` = 1 ")
        Try
            With MassexCmb
                .DataSource = MASSEX
                .DisplayMember = "SEXDESC"
                .ValueMember = "SEX"
                '.BindingContext = BindingContext
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    'Public Function SEXCombobox() As DataTable
    '    connectlocal = conlocal.NewConnection()
    '    Return connectlocal.GetTable("SELECT SEX, SEXDESC FROM MASSEX WHERE [STATUS] = 1")
    'End Function

    Public Sub insertMasprenameData()
        If (STPRENAME <> "" Or FTPRENAME <> "") And SEX.ToString.Length > 0 Then
            If TypeOf STPRENAME Is String And TypeOf SEPRENAME Is String And TypeOf FTPRENAME Is String And TypeOf FEPRENAME Is String Then
                If connect.GetTable("SELECT prename FROM masprename WHERE ftprename = '" & FTPRENAME & "'").Rows.Count > 0 Then
                    MsgBox("มีข้อมูลคำนำหน้านี้อยู่ในระบบแล้ว")
                Else
                    Try
                        connect = CONDB.NewConnection()
                        'connectlocal = conlocal.NewConnection()
                        Dim prenameID As Integer

                        prenameID = connect.ExecuteScalar("INSERT INTO masprename (stprename, seprename, ftprename, feprename, sex, status) VALUES ('" & STPRENAME & "', '" & SEPRENAME & "', '" & FTPRENAME & "', '" & FEPRENAME & "', '" & SEX & "', '" & prenameStatus & "'); SELECT LAST_INSERT_ID();")
                        'connectlocal.ExecuteNonQuery("INSERT INTO MASPRENAME (PRENAME, STPRENAME, SEPRENAME, FTPRENAME, FEPRENAME, SEX, STATUS) VALUES ('" & prenameID & "', '" & STPRENAME & "', '" & SEPRENAME & "', '" & FTPRENAME & "', '" & FEPRENAME & "', '" & SEX & "', '" & prenameStatus & "')")
                        MsgBox("ท่านได้ทำการเพิ่มข้อมูลคำนำหน้าชื่อเรียบร้อยแล้ว")
                    Catch ex As Exception
                        MsgBox("มีเลขรหัส Discharge นี้อยู่ในระบบอยู่แล้ว" & vbCrLf & ex.Message)
                    End Try

                    connect.Dispose()
                End If
            Else
                MsgBox("โปรดกรุณาระบุข้อมูลให้ถูกต้อง ต้องเป็นตัวอักษร")
            End If
        Else
            MsgBox("ท่านต้องทำการกรอกข้อมูลให้ครบถ้วน")
        End If
    End Sub

    Public Sub editMasprenameData()
        If prenameID > 0 And (STPRENAME <> "" Or FTPRENAME <> "") And SEX.ToString.Length > 0 Then
            If TypeOf STPRENAME Is String And TypeOf SEPRENAME Is String And TypeOf FTPRENAME Is String And TypeOf FEPRENAME Is String Then
                Try
                    connect = CONDB.NewConnection()
                    'connectlocal = conlocal.NewConnection()

                    connect.ExecuteNonQuery("UPDATE masprename SET stprename = '" & STPRENAME & "', seprename = '" & SEPRENAME & "', ftprename = '" & FTPRENAME & "', feprename = '" & FEPRENAME & "', sex = '" & SEX & "', status = '" & prenameStatus & "' WHERE prename = '" & prenameID & "'")
                    'connectlocal.ExecuteNonQuery("UPDATE masprename SET STPRENAME = '" & STPRENAME & "', SEPRENAME = '" & SEPRENAME & "', FTPRENAME = '" & FTPRENAME & "', FEPRENAME = '" & FEPRENAME & "', SEX = '" & SEX & "', STATUS = '" & prenameStatus & "' WHERE PRENAME = '" & prenameID & "'")
                    MsgBox("ท่านได้ทำการแก้ไขข้อมูลคำนำหน้าชื่อเรียบร้อยแล้ว")
                Catch ex As Exception
                    MsgBox("มีข้อมูลคำนำหน้าชื่อนี้อยู่ในระบบแล้ว" & vbCrLf & ex.Message)
                End Try

                connect.Dispose()
            Else
                MsgBox("โปรดกรุณาระบุข้อมูลให้ถูกต้อง ต้องเป็นตัวอักษร")
            End If
        Else
            MsgBox("ท่านต้องทำการกรอกข้อมูลให้ครบถ้วน")
        End If
    End Sub

End Class